package com.dao.impl;

import com.bean.Room;
import com.dao.RoomDAO;
import com.util.C3p0Utils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class RoomDAOImpl implements RoomDAO {

    /**
     * BeanListHandler将结果列表封装
     * @return
     * @throws SQLException
     */
    @Override
    public List<Room> selectAll() throws SQLException {

        return C3p0Utils.qr.query("select * from roommsg", new BeanListHandler<Room>(Room.class));
    }

    /**
     * BeanHandler将单个列表封装
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Room selectById(Integer id) throws SQLException {
        return C3p0Utils.qr.query("select * from roommsg  where roomId =?", new BeanHandler<>(Room.class), id);
    }

    @Override
    public boolean deleteById(Integer id) throws SQLException {
        return C3p0Utils.qr.update("delete from roommsg where roomId=?", id) > 0;
    }
    @Override
    public boolean deleteAll(String[] idsArr) throws SQLException {
        StringBuffer sql = new StringBuffer("delete from roommsg where roomId in (");
        for (int i = 0; i < idsArr.length; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length()-1);
        sql.append(")");
        return C3p0Utils.qr.update(sql.toString(), idsArr) > 0;
    }
    @Override
    public boolean insert(Room r) throws SQLException {
        return C3p0Utils.qr.update("insert into roommsg values(null,?,?,?,?,?,?,?,?,?)", r.getRoomNumber(),r.getUnitId(),
                r.getBuildId(),r.getRoomType(),r.getRoomDirection(),r.getRoomStatus(),r.getFloorCode(),r.getRoomArea(),r.getRoomDesc()) > 0;
    }
    @Override
    public boolean update(Room r) throws SQLException {
        return C3p0Utils.qr.update("update roommsg set roomNumber=?,unitId=?,buildId=?,roomType=?,roomDirection=?,roomStatus=?,floorCode=?,roomArea=?,roomDesc=? where roomId=?",
                r.getRoomNumber(),r.getUnitId(),r.getBuildId(),r.getRoomType(),r.getRoomDirection(),r.getRoomStatus(),r.getFloorCode(),r.getRoomArea(),r.getRoomDesc(),r.getRoomId()) > 0;
    }


    /**
     * ScalarHandler用来将单个Object类型封装
     * @return
     * @throws SQLException
     */
    @Override
    public Integer count() throws SQLException {
        int n = C3p0Utils.qr.query("select count(*) from roommsg", new ScalarHandler<Long>()).intValue();
        return n;
    }

    @Override
    public Integer countSearch(String search) throws SQLException {
        int n = C3p0Utils.qr.query("select count(*) from roommsg where concat(roomNumber,unitId,buildId,roomType,roomDirection,roomStatus,floorCode,roomArea,roomDesc) like ?",
                new ScalarHandler<Long>(),"%"+search+"%").intValue();
        return n;
    }

    @Override
    public Integer countSearch(Map map) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(*) from roommsg where 1=1");
        if(map==null){
            return C3p0Utils.qr.query(sql.toString(),new ScalarHandler<Long>()).intValue();
        }
        Set<Map.Entry<String,String>> set = map.entrySet();
        //list存放值  map来取属性
        List list = new ArrayList();
        for(Map.Entry<String,String> entry: set ){
            String key = entry.getKey();
            String value = entry.getValue();
            sql.append(" and "+key+" like ?");
            list.add("%"+value+"%");
        }
        return C3p0Utils.qr.query(sql.toString(),new ScalarHandler<Long>(),list.toArray()).intValue();
    }

    @Override
    public List<Room> selectAllPagination(Integer offset, Integer limit) throws SQLException {
        return C3p0Utils.qr.query("select * from roommsg limit ?,?", new BeanListHandler<>(Room.class), offset, limit);

    }
    @Override
    public List<Room> selectAllPaginationSearch(Integer offset, Integer limit, String search) throws SQLException {
        return C3p0Utils.qr.query("select * from roommsg where concat(roomNumber,unitId,buildId,roomType,roomDirection,roomStatus,floorCode,roomArea,roomDesc) like ? limit ?,?",
                new BeanListHandler<>(Room.class),"%"+search+"%",offset,limit);

    }

    @Override
    public List<Room> selectAllPaginationSearch(Integer offset, Integer limit, Map map) throws SQLException {
        StringBuffer sql = new StringBuffer("select * from roommsg where 1=1");
        if(map==null){
            sql.append(" limit ?,?");
            return C3p0Utils.qr.query(sql.toString(),new BeanListHandler<>(Room.class),offset,limit);
        }
        Set<Map.Entry<String,String>> set = map.entrySet();
        //list存放值  map来取属性
        List list = new ArrayList();
        for(Map.Entry<String,String> entry: set ){
            String key = entry.getKey();
            String value = entry.getValue();
            sql.append(" and "+key+" like ?");

            list.add("%"+value+"%");
        }
        sql.append(" limit ?,?");
        list.add(offset);
        list.add(limit);
        return C3p0Utils.qr.query(sql.toString(),new BeanListHandler<>(Room.class),list.toArray());
    }

}
